﻿using System;
using System.Collections.Generic;
using System.Net;
using System.Text.RegularExpressions;

namespace MeTool.Sql
{
    /// <summary>SqlEntity的帮助类. 此类实现了若干功能, 都是辅助性质的, 面向一些零散的边缘性的需求的实现.
    /// <para>若要生成Sql,请使用SqlHelper</para>
    /// </summary>
    public static class Helper
    {
        /// <summary>
        /// 值为 Null 或为 True 时返回 True, 否则返回 False
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool NullIsTrue(bool? value)
        {
            return value == null || value.Value;
        }

        /// <summary>
        /// 值不为 Null 或为 True 时返回 True, 否则返回 False
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool NullIsFalse(bool? value)
        {
            return !value == null && value.Value;
        }

        /// <summary>
        /// 如果数据为Null,替换为另一个数据
        /// </summary>
        /// <param name="value">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的数据,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static object NullValue(object value, object replaceValue)
        {
            return value == DBNull.Value ? replaceValue : value;
        }

        /// <summary>
        /// 如果数据为Null,替换为一个整数
        /// </summary>
        /// <param name="value">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="ReplaceValue">要替换的整数,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static int NullValue(object value, int replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToInt32(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个整数
        /// </summary>
        /// <param name="theValue">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的整数,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static long NullValue(object value, long replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToInt64(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个Double
        /// </summary>
        /// <param name="theValue">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的Double,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static double NullValue(object value, double replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToDouble(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个String
        /// </summary>
        /// <param name="value">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的String,只有被替换的数据为Null才会替换</param>
        /// <param name="nullReplace"></param>
        /// <param name="toTrim"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string NullValue(object value, string replaceValue, bool nullReplace = false, bool toTrim = false)
        {
            var dText = value == DBNull.Value ? replaceValue : (value?.ToString() ?? "");
            if (nullReplace && dText == null) dText = "";
            if (toTrim && dText != null) dText = dText.Trim();
            return dText;
        }

        /// <summary>
        /// 如果数据为Null,替换为一个Date
        /// </summary>
        /// <param name="value">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的Date,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static DateTime NullValue(object value, DateTime replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToDateTime(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个Boolean
        /// </summary>
        /// <param name="theValue">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="replaceValue">要替换的Boolean,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static bool NullValue(object value, bool replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToBoolean(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个Decimal
        /// </summary>
        /// <param name="theValue">被替换的数据,如果不为Null不会被替换</param>
        /// <param name="ReplaceValue">要替换的Boolean,只有被替换的数据为Null才会替换</param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static decimal NullValue(object value, decimal replaceValue)
        {
            return value == DBNull.Value ? replaceValue : Convert.ToDecimal(value);
        }

        /// <summary>
        /// 如果数据为Null,替换为一个Byte数组
        /// </summary>
        /// <param name="value"></param>
        /// <param name="replaceValue"></param>
        /// <returns></returns>
        public static byte[] NullValue(object value, byte[] replaceValue)
        {
            return value == DBNull.Value ? replaceValue : (byte[])value;
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(string value)
        {
            return "'" + value.Replace("'", "''") + "'";
        }

        /// <summary>
        /// 获取值的SQL表达式
        /// </summary>
        /// <param name="value"></param>
        /// <param name="theLike"></param>
        /// <returns></returns>
        public static string GetSqlText(string value, EmTextLike theLike = EmTextLike.Normal)
        {
            value = value.Replace("'", "''");
            switch (theLike)
            {
                case EmTextLike.Left:
                    return "'" + value + "%'";
                case EmTextLike.Right:
                    return "'%" + value + "'";
                case EmTextLike.AllPos:
                    return "'%" + value + "%'";
                default:
                    return "'" + value + "'";
            }
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(int value)
        {
            return value.ToString();
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(long value)
        {
            return value.ToString();
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="theValue"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(double value)
        {
            return value.ToString();
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="theValue"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(decimal value)
        {
            return value.ToString();
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(bool value)
        {
            return value ? "1" : "0";
        }

        /// <summary>
        /// 获取值的SQL表达式
        /// </summary>
        /// <returns></returns>
        public static string GetSqlText(byte[] value)
        {
            return value == null ? "null" : "0x" + BitConverter.ToString(value).Replace("-", "");
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(Guid value)
        {
            return "'" + value.ToString() + "'";
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string GetSqlText(DateTime value, EmDateFmt format = EmDateFmt.TimeMilli)
        {
            return "'" + FormatDate(value, format) + "'";
        }

        /// <summary>
        /// 获取值的SQL表达式
        /// </summary>
        /// <returns></returns>
        public static string GetSqlText(DateTime value1, DateTime value2, EmDateFmt format = EmDateFmt.TimeMilli)
        {
            string dValue1Text = "";
            string dValue2Text = "";
            switch (format)
            {
                case EmDateFmt.Year:
                    dValue1Text = value1.Year.ToString() + "-01-01";
                    dValue2Text = value2.Year.ToString() + "-12-31 23:59:59.997";
                    break;
                case EmDateFmt.YearMonth:
                    dValue1Text = value1.ToString("yyyy-MM") + "-01";
                    dValue2Text = value2.ToString("yyyy-MM") + "-" + DateTime.DaysInMonth(value2.Year, value2.Month).ToString() + " 23:59:59.997";
                    break;
                case EmDateFmt.Date:
                    dValue1Text = value1.ToString("yyyy-MM-dd");
                    dValue2Text = value2.ToString("yyyy-MM-dd") + " 23:59:59.997";
                    break;
                case EmDateFmt.DateHour:
                    dValue1Text = value1.ToString("yyyy-MM-dd HH") + ":00:00";
                    dValue2Text = value2.ToString("yyyy-MM-dd HH") + ":59:59.997";
                    break;
                case EmDateFmt.DateHourMinute:
                    dValue1Text = value1.ToString("yyyy-MM-dd HH:mm") + ":00";
                    dValue2Text = value2.ToString("yyyy-MM-dd HH:mm") + ":59.997";
                    break;
                case EmDateFmt.Time:
                    dValue1Text = value1.ToString("yyyy-MM-dd HH:mm:ss");
                    dValue2Text = value2.ToString("yyyy-MM-dd HH:mm:ss") + ".997";
                    break;
                case EmDateFmt.TimeMilli:
                    dValue1Text = value1.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    dValue2Text = value2.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    break;
                case EmDateFmt.Month:
                    dValue1Text = value1.ToString("MM");
                    dValue2Text = value2.ToString("MM");
                    break;
                case EmDateFmt.Day:
                    dValue1Text = value1.ToString("dd");
                    dValue2Text = value2.ToString("dd");
                    break;
                case EmDateFmt.Hour:
                    dValue1Text = value1.ToString("hh");
                    dValue2Text = value2.ToString("hh");
                    break;
                case EmDateFmt.Minute:
                    dValue1Text = value1.ToString("mm");
                    dValue2Text = value2.ToString("mm");
                    break;
                case EmDateFmt.Second:
                    dValue1Text = value1.ToString("ss");
                    dValue2Text = value2.ToString("ss");
                    break;
                case EmDateFmt.MilliSecond:
                    dValue1Text = value1.ToString("fff");
                    dValue2Text = value2.ToString("fff");
                    break;
            }
            return "'" + dValue1Text + "' and '" + dValue2Text + "'";
        }

        /// <summary>
        /// 按照emDateFmt的值格式化日期值
        /// </summary>
        /// <param name="value"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        /// <remarks></remarks>
        public static string FormatDate(DateTime value, EmDateFmt format)
        {
            string FormatDate;
            switch (format)
            {
                case EmDateFmt.Year:
                    FormatDate = value.ToString("yyyy");
                    break;
                case EmDateFmt.YearMonth:
                    FormatDate = value.ToString("yyyy-MM");
                    break;
                case EmDateFmt.Date:
                    FormatDate = value.ToString("yyyy-MM-dd");
                    break;
                case EmDateFmt.DateHour:
                    FormatDate = value.ToString("yyyy-MM-dd HH");
                    break;
                case EmDateFmt.DateHourMinute:
                    FormatDate = value.ToString("yyyy-MM-dd HH:mm");
                    break;
                case EmDateFmt.Time:
                    FormatDate = value.ToString("yyyy-MM-dd HH:mm:ss");
                    break;
                case EmDateFmt.TimeMilli:
                    FormatDate = value.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    break;
                case EmDateFmt.Month:
                    FormatDate = value.ToString("MM");
                    break;
                case EmDateFmt.Day:
                    FormatDate = value.ToString("dd");
                    break;
                case EmDateFmt.Hour:
                    FormatDate = value.ToString("HH");
                    break;
                case EmDateFmt.Minute:
                    FormatDate = value.ToString("mm");
                    break;
                case EmDateFmt.Second:
                    FormatDate = value.ToString("ss");
                    break;
                case EmDateFmt.MilliSecond:
                    FormatDate = value.ToString("fff");
                    break;
                default:
                    FormatDate = "";
                    break;
            }
            return FormatDate;
        }

        /// <summary>
        /// 生成一段Sql语句,意义为: 把一个日期类型的数据转换成指定格式的文本
        /// </summary>
        /// <param name="columnName"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        public static string GetDateFieldSql(string columnName, EmDateFmt format)
        {
            if (format > EmDateFmt.TimeMilli)
            {
                string dFormat;
                switch (format)
                {
                    case EmDateFmt.Month:
                        dFormat = "mm";
                        break;
                    case EmDateFmt.Day:
                        dFormat = "dd";
                        break;
                    case EmDateFmt.Hour:
                        dFormat = "hh";
                        break;
                    case EmDateFmt.Minute:
                        dFormat = "n";
                        break;
                    case EmDateFmt.Second:
                        dFormat = "ss";
                        break;
                    default:
                        dFormat = "ms";
                        break;
                }
                return "cast(datepart(" + dFormat + "," + columnName + ") as varchar(4))";
            }

            string dLength;
            switch (format)
            {
                case EmDateFmt.Year:
                    dLength = "4";
                    break;
                case EmDateFmt.YearMonth:
                    dLength = "7";
                    break;
                case EmDateFmt.Date:
                    dLength = "10";
                    break;
                case EmDateFmt.DateHour:
                    dLength = "13";
                    break;
                case EmDateFmt.DateHourMinute:
                    dLength = "16";
                    break;
                case EmDateFmt.Time:
                    dLength = "19";
                    break;
                default:
                    dLength = "23";
                    break;
            }
            if (dLength == "23")
                return "convert(nchar(23)," + columnName + ",121)";
            else
                return "convert(nchar(" + dLength + ")," + columnName + ",120)";
        }

        /// <summary>
        /// 返回值的SQL表示法
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string GetSqlValue(object value)
        {
            if (value == null)
                return "null";
            else if (value is string dstring)
                return GetSqlText(dstring);
            else if (value is DateTime dt)
                return GetSqlText(dt);
            else if (value is DateTimeOffset doff)
                return GetSqlText(doff.ToString("hh:mm:ss.fff"));
            else if (value is int dint)
                return GetSqlText(dint);
            else if (value is long dlong)
                return GetSqlText(dlong);
            else if (value is decimal ddecimal)
                return GetSqlText(ddecimal);
            else if (value is double ddouble)
                return GetSqlText(ddouble);
            else if (value is float dsingle)
                return GetSqlText(dsingle);
            else if (value is Guid dguid)
                return GetSqlText(dguid);
            else if (value is short dshort)
                return GetSqlText(dshort);
            else if (value is ushort dint16)
                return GetSqlText(dint16);
            else if (value is uint duint)
                return GetSqlText(duint);
            else if (value is ulong dulong)
                return GetSqlText((long)dulong);
            else if (value is byte dbyte)
                return GetSqlText(dbyte);
            else if (value is char dchar)
                return GetSqlText(dchar);
            else if (value is bool dbool)
                return GetSqlText(dbool);
            else
                return GetSqlText(value.ToString());
        }

        /// <summary>
        /// 或者系统网卡第一个有效的IPv4地址
        /// </summary>
        /// <returns></returns>
        public static string GetCurrentIPAddress()
        {
            var dAddressArray = Dns.GetHostAddresses(Dns.GetHostName());
            var dIP = "";
            var dIP2 = "";
            var dRegex = new Regex("^(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|[1-9])(\\.(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)){3}$");
            foreach (IPAddress dAddress in dAddressArray)
            {
                if (dAddress.IsIPv6LinkLocal) continue;
                if (dAddress.IsIPv6Multicast) continue;
                if (dAddress.IsIPv6SiteLocal) continue;
                if (dAddress.IsIPv6Teredo) continue;
                var dName = dAddress.ToString();
                if (dRegex.IsMatch(dName)) dIP2 = dName;
                if (dName != "127.0.0.1") dIP = dName;
            }
            if (dIP.Length == 0) dIP = dIP2;
            return dIP;
        }

        /// <summary>
        /// 获取系统所有有效的IPv4地址
        /// </summary>
        /// <param name="removeLocalIP"></param>
        /// <returns></returns>
        public static List<string> GetCurrentIPAddressList(bool removeLocalIP = false)
        {
            var dList = new List<string>();
            var dAddressArray = Dns.GetHostAddresses(Dns.GetHostName());
            var dRegex = new Regex("^(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|[1-9])(\\.(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)){3}$");
            foreach (var dAddress in dAddressArray)
            {
                if (dAddress.IsIPv6LinkLocal) continue;
                if (dAddress.IsIPv6Multicast) continue;
                if (dAddress.IsIPv6SiteLocal) continue;
                if (dAddress.IsIPv6Teredo) continue;
                var dName = dAddress.ToString();
                if (dRegex.IsMatch(dName))
                {
                    if (!removeLocalIP || dName != "127.0.0.1") dList.Insert(0, dName);
                }
            }
            return dList;
        }
    }
}
